package com.situ.company.util;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.ResourceBundle;
import java.sql.ResultSet;

//工具类
public class JDBCUtil {// DBUtil DBTool

	private JDBCUtil() {

	}

	private static ResourceBundle rs = ResourceBundle.getBundle("com.situ.company.util.jdbc");
	private static String driver = rs.getString("driver");
	private static String url = rs.getString("url");
	private static String user = rs.getString("user");
	private static String pass = rs.getString("pass");
	static {
		try {
			Class.forName(driver);

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public static Connection getConnection() throws Exception {
		try {
			return DriverManager.getConnection(url, user, pass);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;

	}

	public static Connection getConection1() throws SQLException {
		return DriverManager.getConnection(url, user, pass);
	}
	
	
	
	
	 public static Integer update(String sql,Object...values) {
	
		 return update(sql, Arrays.asList(values));
		 
		 
	 }
	
	
	//数组中的元素类型是引用类型=》aslist返回的list中元素就是数组中的元素
	//数组中元素是基本数据类型=》aslist返回的list中元素只有一个，即传入进来的这个数组
	/**
	 * 
	 * @param conn
	 * @param st
	 * 执行DML
	 */
	 
	 public static Integer update(String sql,List<Object> values) {
		 Connection conn = null;
		 PreparedStatement ps = null;
		 try {
			 conn = getConnection();
			 ps = conn.prepareStatement(sql);
			 for(int i=0;i<values.size();i++) 
					ps.setObject(i+1, values.get(i));
					return ps.executeUpdate();
				
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			close(conn, ps);
		}
		 return null;
	 }
	//执行DQL
	 /**
	  * sql:即将执行的sql
	  * values:执行的sql中的数据（？）
	  * clazz:即将反射得到的实体类的类的描述
	  * fields:映射关系key=属性名 values=字段名
	  */
	public static <T>List<T> queryList(String sql,List<Object> values,Class<T> clazz,Map<String,String>fields) {
		Connection conn = null;
		 PreparedStatement ps = null;
		 ResultSet rs =null;
		List<T>	result = new ArrayList<>();
		 try {
			 conn = getConnection();
			 ps = conn.prepareStatement(sql);
			 for(int i=0;i<values.size();i++) 
				ps.setObject(i+1, values.get(i));
				rs = ps.executeQuery();
				while(rs.next()) {
					T model = clazz.newInstance();
					for(Entry<String, String> entry:fields.entrySet()) {
						Field field = clazz.getDeclaredField(entry.getKey());
						field.setAccessible(true);
						field.set(model, rs.getObject(entry.getValue()));
					}
					result.add(model);
				}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			close(conn, ps);
		}
		return null;
	}
	
	public static <T>List<T> queryList(String sql,List<Object> values,Class<T> clazz,List<String> props) {
		Map<String,String> fields = new HashMap<>();
		for(String p:props)
			fields.put(p, p);
		return queryList(sql,values,clazz,fields);
	}
	
	public static <T>List<T> queryList(String sql,List<Object> values,Class<T> clazz) {
		Map<String,String> fields = new HashMap<>();
		for(Field p:clazz.getDeclaredFields())
			fields.put(p.getName(), p.getName());
		return queryList(sql,values,clazz,fields);
	}
	
	public static <T> T queryModel(String sql,List<Object> values,Class<T> clazz,Map<String,String>fields) {
		Connection conn = null;
		 PreparedStatement ps = null;
		 ResultSet rs =null;
		 
		 try {
			 conn = getConnection();
			 ps = conn.prepareStatement(sql);
			 for(int i=0;i<values.size();i++) 
				ps.setObject(i+1, values.get(i));
				rs = ps.executeQuery();
				if(rs.next()) {
					T model = clazz.newInstance();
					for(Entry<String, String> entry:fields.entrySet()) {
						Field field = clazz.getDeclaredField(entry.getKey());
						field.setAccessible(true);
						field.set(model, rs.getObject(entry.getValue()));
					}
					return model;
				}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			close(conn, ps);
		}
		return null;
	}
	
	public static <T> T queryModel(String sql,List<Object> values,Class<T> clazz,List<String> props) {
		Map<String,String> fields = new HashMap<>();
		for(String p:props)
			fields.put(p, p);
		return queryModel(sql,values,clazz,fields);
	}
	
	public static <T> T queryModel(String sql,List<Object> values,Class<T> clazz) {
		Map<String,String> fields = new HashMap<>();
		for(Field p:clazz.getDeclaredFields())
			fields.put(p.getName(), p.getName());
		return queryModel(sql,values,clazz,fields);
	}
	
	/**
	 * 
	 * @param sql
	 * @param values
	 * @return
	 */
	public static Integer queInt(String sql,List<Object> values) {
		Connection conn = null;
		 PreparedStatement ps = null;
		 try {
			 conn = getConnection();
			 ps = conn.prepareStatement(sql);
			 for(int i=0;i<values.size();i++) 
					ps.setObject(i+1, values.get(i));
					ResultSet rs =  ps.executeQuery();
					if(rs.next())
						return rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			close(conn, ps);
		}
		 return null;
	}
	
	
	public static void close(Connection conn, Statement st) {
		close(conn, st, null);
	}

	public static void close(Connection conn, Statement st, ResultSet rd) {
		try {
			if (rd != null) {
				rd.close();
				rd = null;
			}
			if (st != null) {
				st.close();
				st = null;
			}
			if (conn != null) {
				conn.close();
				conn = null;
			}
		} catch (Exception e) {  
			e.printStackTrace();
		}
	}
	public static void main(String[] args) throws Exception {
		Connection con =JDBCUtil.getConnection();
		System.out.println(con);
		JDBCUtil.close(con, null);
	}
	
}
